package org.example.dao;

import org.example.entity.Record;
import org.example.util.DBUtil;
import org.example.util.DateUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * DAO类-Record，对 record 表进行增改删查操作，消费记录数据表的操作
 */
public class RecordDAO {
    /**
     * 添加，消费记录
     *
     * @param spend    花费金额
     * @param cid      消费种类 id
     * @param comment  备注
     * @param location 消费地点
     * @param date     消费日期
     */
    public static void add(double spend, int cid, String comment, String location, Date date) {
        String sql = "insert into record (`spend`,`cid`,`comment`,`location`,`date`) values (?,?,?,?,?)";
        try (Connection c = DBUtil.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setDouble(1, spend);
            ps.setInt(2, cid);
            ps.setString(3, comment);
            ps.setString(4, location);
            ps.setDate(5, DateUtil.util2sql(date));
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 修改消费记录（通过查找 id，修改花费 spend，消费种类 cid，消费的备注 comment，消费日期 data）
     *
     * @param id       消费记录 id
     * @param spend    花费金额
     * @param cid      消费种类 id
     * @param comment  备注
     * @param location 消费地点
     * @param date     消费日期
     */
    public static void update(int id, double spend, int cid, String comment, String location, Date date) {
        String sql = "update record set `spend`=? , `cid`=? , `comment`=?, `location`=? , `date`=? where id=?";
        try (Connection c = DBUtil.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setDouble(1, spend);
            ps.setInt(2, cid);
            ps.setString(3, comment);
            ps.setString(4, location);
            ps.setDate(5, DateUtil.util2sql(date));
            ps.setInt(6, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 通过查找 id，删除消费记录
     *
     * @param id 消费记录 id
     */
    public static void delete(int id) {
        String sql = "delete from record where id = ?";
        try (Connection c = DBUtil.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 通过消费种类 Category 的 cid，查找返回 List\<Record> （Record列表）
     *
     * @param cid 消费种类 Category 的 cid
     * @return List\<Record> 该分类下所有的消费记录
     */
    public static List<Record> listByCategoryId(int cid) {
        String sql = "select * from record where `cid` = ?";
        List<Record> records = new ArrayList<>();
        try (Connection c = DBUtil.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1, cid);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Record record = new Record(
                        rs.getInt("id"),
                        rs.getDouble("spend"),
                        rs.getInt("cid"),
                        rs.getString("comment"),
                        rs.getString("location"),
                        rs.getDate("date"));
                records.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }


    /**
     * 获得今天的 List\<Record> （Record列表）
     */
    public static List<Record> listToday() {
        return list(new Date());
    }


    /**
     * 获得当月的 List\<Record> （Record列表）
     */
    public static List<Record> listThisMonth() {
        return list(DateUtil.monthBegin(), DateUtil.monthEnd());
    }

    /**
     * 获取指定月的 record 数据
     *
     * @param startDay 指定月的第一天的 Date
     * @return record 的列表
     */
    public static List<Record> listAnyMonthByStartDay(Date startDay) {
        Date endDay = DateUtil.monthEnd(startDay);
        return list(startDay, endDay);
    }


    /**
     * 用于统计图数据
     *
     * @return 一个当前月每天的 record 列表，这里的List\<Record>不是数据库中原始的记录，而是把一天的都加起来后消费金额
     */
    public static List<Record> statisticData() {
        List<Record> result = new ArrayList<>();
        List<Record> listThisMonth = listThisMonth();
        Date monthBegin = DateUtil.monthBegin();
        int thisMonthTotalDay = DateUtil.thisMonthTotalDay();
        Calendar calendar = Calendar.getInstance();
        for (int i = 0; i < thisMonthTotalDay; i++) {
            calendar.setTime(monthBegin);
            calendar.add(Calendar.DATE, i);
            Date theDayOfThisMonth = calendar.getTime();

            // 计算每天的总消费
            Record resultItem = new Record();
            double dayTotalSpend = 0;
            for (Record record : listThisMonth) {
                if (record.getDate().equals(theDayOfThisMonth))
                    dayTotalSpend += record.getSpend();
            }
            resultItem.setSpend(dayTotalSpend);
            result.add(resultItem);
        }
        return result;
    }


    /**
     * start 开始日期，end 结束日期，查找返回 List\<Record> （Record列表）
     */
    private static List<Record> list(Date start, Date end) {
        String sql = "select * from record where `date` >= ? and `date` <= ? order by date asc";
        List<Record> records = new ArrayList<>();
        try (Connection c = DBUtil.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setDate(1, DateUtil.util2sql(start));
            ps.setDate(2, DateUtil.util2sql(end));
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Record record = new Record(
                        rs.getInt("id"),
                        rs.getDouble("spend"),
                        rs.getInt("cid"),
                        rs.getString("comment"),
                        rs.getString("location"),
                        rs.getDate("date"));
                records.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }


    /**
     * 通过日期Date，查找返回 List\<Record> （Record列表）
     */
    private static List<Record> list(Date day) {
        String sql = "select * from record where `date` = ?";
        List<Record> records = new ArrayList<>();
        try (Connection c = DBUtil.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setDate(1, DateUtil.util2sql(day));
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Record record = new Record(
                        rs.getInt("id"),
                        rs.getDouble("spend"),
                        rs.getInt("cid"),
                        rs.getString("comment"),
                        rs.getString("location"),
                        rs.getDate("date"));
                records.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }
}